﻿using System;
using System.Collections.Generic;
using System.Text;
using Home.Utils;
using System.Reflection;
using System.Data.OleDb;
using NhanAi.Entity;
namespace NhanAi.DAO
{
    class UserDao : DBConnection
    {
        public int Count()
        {
            OleDbCommand cmd = new OleDbCommand("select count(*) from HmPost",conn);
            if (Connect())
            {
                int count = 0;
                count=(int)cmd.ExecuteScalar();
                Disconnect();
                return count;
            }
            return 0;
        }
        public void Create(User u)
        {
            NonQueryCommandBuilder nb = new NonQueryCommandBuilder("HmUser");
            if (Connect())
            {
                nb.AddValueField("UserLogin", u.UserLogin);
                nb.AddValueField("UserPassword", u.UserPassword);
                nb.AddValueField("NiceName", u.Nicename);
                nb.AddValueField("Email", u.Email);
                nb.AddValueField("ActivationKey", u.ActivationKey);
                nb.AddValueField("RegisteredDate", u.RegistedDate.ToString());
                nb.AddValueField("UserStatus", u.Status);
                nb.AddValueField("UserRole", u.UserRole);

                OleDbCommand cmd = nb.GetCreateCommand(conn);

                cmd.ExecuteNonQuery();
                cmd.CommandText = "select @@IDENTITY";
                u.Id = (int)cmd.ExecuteScalar();
                Disconnect();
            }

        }
        public void Update(User u)
        {
            NonQueryCommandBuilder nb = new NonQueryCommandBuilder("HmUser");
            if (Connect())
            {

                //nb.AddValueField("ID",u.Id);
                nb.AddValueField("UserLogin", u.UserLogin);
                nb.AddValueField("UserPassword", u.UserPassword);
                nb.AddValueField("NiceName", u.Nicename);
                nb.AddValueField("Email", u.Email);
                nb.AddValueField("ActivationKey", u.ActivationKey);
                nb.AddValueField("RegisteredDate", u.RegistedDate);
                nb.AddValueField("UserStatus", u.Status);
                nb.AddValueField("UserRole", u.UserRole);
                nb.AddConditionField("ID", u.Id);
                OleDbCommand cmd = nb.GetUpdateCommand(conn, true);
                cmd.ExecuteNonQuery();
                Disconnect();
            }

        }
        public void Delele(int uid)
        {
            NonQueryCommandBuilder nb = new NonQueryCommandBuilder("HmUser");
            if (Connect())
            {

                nb.AddValueField("ID", uid);
                OleDbCommand cmd = nb.GetDeleteCommand(conn, true);

                Disconnect();
            }

        }
        public void Delete(string ids)
        {
            NonQueryCommandBuilder nb = new NonQueryCommandBuilder("HmUser");
            OleDbCommand cmd = new OleDbCommand("delete from HmUser where Id in (" + ids + ")", conn);
            if (Connect())
            {
                cmd.ExecuteNonQuery();
                Disconnect();
            }
        }
        public User Read(int id)
        {
            User u = null;
            if (Connect())
            {
                OleDbCommand cmd = new OleDbCommand("select * from HmUser where ID=@id", conn);
                cmd.Parameters.AddWithValue("@id", id);
                OleDbDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    u = new User();
                    u.Id = dr.GetInt32(0);
                    u.UserLogin = dr.GetString(1);
                    u.UserPassword = dr.GetString(2);
                    u.Nicename = dr.GetString(3);
                    u.Email = dr.GetString(4);
                    u.RegistedDate = dr.GetDateTime(5);
                    u.ActivationKey = dr.GetString(6);
                    u.Status = dr.GetInt16(7);
                    u.UserRole = dr.GetInt16(8);

                }
                Disconnect();
            }
            return u;
        }
        public User Read(string username)
        {
            User u = null;
            if (Connect())
            {
                OleDbCommand cmd = new OleDbCommand("select * from HmUser where UserLogin=@username", conn);
                cmd.Parameters.AddWithValue("@username", username);
                OleDbDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    u = new User();
                    u.Id = dr.GetInt32(0);
                    u.UserLogin = dr.GetString(1);
                    u.UserPassword = dr.GetString(2);
                    u.Nicename = dr.GetString(3);
                    u.Email = dr.GetString(4);
                    u.RegistedDate = dr.GetDateTime(5);
                    u.ActivationKey = dr.GetString(6);
                    u.Status = dr.GetInt16(7);
                    u.UserRole = dr.GetInt16(8);

                }
                Disconnect();
            }
            return u;
        }
        public List<User> ReadMany(int pageSize, int pageNum, string username, string email, string activationKey, int status,int userRole, out int total)
        {
            List<User> list = new List<User>();
            OleDbCommand cmd = new OleDbCommand("select count(*) from HmUser", conn);
            total=0;
            if (Connect())
            {
                total = (int)cmd.ExecuteScalar();
                QueryCommandBuilder qb = new QueryCommandBuilder("select TOP #pageSize * from (select TOP #total * from HmUser where 1=1");
                qb.AddSuffix("order by Id asc) order by Id desc");
                qb.AddParam("pageSize", pageSize);
                int tmp = total - (pageNum - 1) * pageSize;
                qb.AddParam("total", tmp > 0 ? tmp : 1);
                if (!String.IsNullOrEmpty(username)) qb.AddWithValue("UserLogin", username);
                if (!String.IsNullOrEmpty(email)) qb.AddWithValue("Email", email, "like");
                if (!String.IsNullOrEmpty(activationKey)) qb.AddWithValue("ActivationKey", activationKey);
                if (status > 0) qb.AddWithValue("UserStatus", status);
                if (userRole > 0) qb.AddWithValue("UserRole",userRole);
                cmd = qb.GetBuildedCommand(conn);

                OleDbDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    User u = new User();
                    u.Id = dr.GetInt32(0);
                    u.UserLogin = dr.GetString(1);
                    u.UserPassword = dr.GetString(2);
                    u.Nicename = dr.GetString(3);
                    u.Email = dr.GetString(4);
                    u.RegistedDate = dr.GetDateTime(5);
                    u.ActivationKey = dr.GetString(6);
                    u.Status=(int)dr.GetInt16(7);
                    u.UserRole = (int)dr.GetInt16(8);
                    list.Add(u);
                }
            }
            return list;
        }
        public List<string> GetFollowedEmails(int group) {
            List<string> emails = new List<string>();
            OleDbCommand cmd = new OleDbCommand("select distinct Email from HmUser where UserRole=@group",conn);
            cmd.Parameters.AddWithValue("@group",group);
            if (Connect())
            {
                OleDbDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    emails.Add(dr.GetString(0));
                }
            }
            return emails;
        }
    }
}
